<?php


namespace app\service\alone\Excel;


use app\service\alone\BaseService;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xls;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExportService extends BaseService
{
    /**
     * 导出文件 excel
     *
     * @param string $fileName 文件名称
     * @param array $data 数据    [ [ 'title' => '工作薄标题', 'list' => '工作薄数据' ] ]
     * @param bool $isStorage 是否存储在本地
     * @return array
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function export(string $fileName, array $data, bool $isStorage = false)
    {
        $excel = new Spreadsheet();

        // 设置工作薄
        foreach ($data as $k => $v) {
            $this->setSheet($excel, $k, $v['title'], $v['list']);
        }

        // 文件名称
        $fileName .= '-' . date('Y-m-d');

        // 下载
        return $this->download($excel, $fileName, $isStorage, 'Xls');
    }

    /**
     * 设置工作薄
     *
     * @param Spreadsheet $excel
     * @param int $key
     * @param string $title
     * @param array $data
     * @return void
     */
    private function setSheet(Spreadsheet $excel, int $key, string $title, array $data): void
    {
        // 计算所需列
        $column = $this->calculationColumn(array_reduce($data, function (int $result, array $item): int {
            return max(count($item), $result);
        }, 0));

        // 第一个则使用默认的工作薄, 否则创建工作薄
        $sheet = $key === 0 ? $excel->getActiveSheet() : $excel->createSheet();

        // 设置自动宽度
        foreach ($column as $v) {
            $sheet->getColumnDimension($v)->setAutoSize(true);
        }

        // 设置 sheet 标题
        $sheet->setTitle($title);

        // 设置数据
        foreach ($data as $k => $v) {
            $index = $k + 1;

            if (!$v) continue;

            // 设置内容
            foreach ($column as $key => $val) {
                if (!isset($v[$key])) continue;

                $sheet->setCellValue($val . $index, $v[$key]);
            }
        }
    }

    /**
     * 计算所需列
     *
     * @param int $count
     * @return array
     */
    private function calculationColumn(int $count): array
    {
        // 初始化变量
        list($columnName, $column, $str) = [
            array_map(function ($v) {
                return chr($v);
            }, range(65, 90)),
            [],
            ''
        ];

        // 计算所需列
        for ($i = 0; $i < $count; $i++) {
            if ($i != 0 && $i % 26 == 0) {
                $index = strlen($str) - 1;

                // 判断每个字符都满足条件
                while ($index >= 0) {
                    if ($str[$index] != 'Z') {
                        $key = array_search($str[$index], $columnName);

                        $str[$index] = $columnName[$key + 1];

                        break;
                    }

                    $index--;
                }

                // 如都满足条件，则重新定义字符串A
                if ($index < 0) $str = implode('', array_fill_keys(range(0, strlen($str)), $columnName[0]));

                // 第一次定义有效
                $str = $str ?: $columnName[0];
            }

            array_push($column, $str . $columnName[$i % 26]);
        }

        return $column;
    }

    /**
     * 下载 excel 文档
     *
     * @param $excel
     * @param string $fileName
     * @param bool $isStorage
     * @param string $format
     * @return array
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    private function download($excel, string $fileName, bool $isStorage, string $format = 'Xls'): array
    {
        // 清除缓存
        if (ob_get_length()) ob_end_clean();

        // 存储在本地，或下载
        if ($isStorage) {
            $filesystem = config('filesystem');
            $path = $filesystem['location']['tmp'] . DIRECTORY_SEPARATOR . 'excels' . DIRECTORY_SEPARATOR . $fileName;

            // 目录不存在，则创建
            if (!is_dir($dir = $filesystem['disks']['public']['root'] . DIRECTORY_SEPARATOR . $filesystem['location']['tmp'] . DIRECTORY_SEPARATOR . 'excels')) mkdir($dir, 0775);

            $writer = $format == 'Xls' ? new Xls($excel) : new Xlsx($excel);
            $writer->save($filesystem['disks']['public']['root'] . DIRECTORY_SEPARATOR . $path . '.' . strtolower($format));

            return echoArr(200, '请求成功', [
                'path' => $path . '.' . strtolower($format)
            ]);
        } else {
            // $format只能为 Xlsx 或 Xls
            if ($format == 'Xlsx') {
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            } elseif ($format == 'Xls') {
                header('Content-Type: application/vnd.ms-excel');
            }

            header("Content-Disposition: attachment;filename=" . $fileName . '.' . strtolower($format));
            header('Cache-Control: max-age=0');
            $objWriter = IOFactory::createWriter($excel, $format);

            $objWriter->save('php://output');

            //以下为需要用到IE时候设置
            // If you're serving to IE 9, then the following may be needed
            header('Cache-Control: max-age=1');

            // If you're serving to IE over SSL, then the following may be needed
            header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
            header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
            header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
            header('Pragma: public'); // HTTP/1.0
            exit;
        }
    }
}